from database import DatabaseManager


class StudentScoreManager:
    def __init__(self):
        self.db = DatabaseManager()
        
    def connect(self):
        """连接数据库并创建表"""
        if self.db.connect():
            self.db.create_table()
            return True
        return False
    
    def disconnect(self):
        """断开数据库连接"""
        self.db.disconnect()
    
    def add_score(self, student_id, course_id, score):
        """添加成绩记录"""
        query = """
        INSERT INTO student_scores (student_id, course_id, score) 
        VALUES (%s, %s, %s)
        """
        return self.db.execute_update(query, (student_id, course_id, score))
    
    def update_score(self, student_id, course_id, score):
        """修改成绩记录"""
        query = """
        UPDATE student_scores 
        SET score = %s, updated_at = CURRENT_TIMESTAMP 
        WHERE student_id = %s AND course_id = %s
        """
        return self.db.execute_update(query, (score, student_id, course_id))
    
    def delete_score(self, student_id, course_id):
        """删除成绩记录"""
        query = """
        DELETE FROM student_scores 
        WHERE student_id = %s AND course_id = %s
        """
        return self.db.execute_update(query, (student_id, course_id))
    
    def query_all_scores(self):
        """查询所有成绩记录"""
        query = """
        SELECT student_id, course_id, score, created_at, updated_at 
        FROM student_scores 
        ORDER BY student_id, course_id
        """
        return self.db.execute_query(query)
    
    def query_scores_by_student(self, student_id):
        """按学号查询成绩"""
        query = """
        SELECT student_id, course_id, score, created_at, updated_at 
        FROM student_scores 
        WHERE student_id = %s
        ORDER BY course_id
        """
        return self.db.execute_query(query, (student_id,))
    
    def query_scores_by_course(self, course_id):
        """按课程编号查询成绩"""
        query = """
        SELECT student_id, course_id, score, created_at, updated_at 
        FROM student_scores 
        WHERE course_id = %s
        ORDER BY student_id
        """
        return self.db.execute_query(query, (course_id,))
    
    def query_score(self, student_id, course_id):
        """查询特定学生的特定课程成绩"""
        query = """
        SELECT student_id, course_id, score, created_at, updated_at 
        FROM student_scores 
        WHERE student_id = %s AND course_id = %s
        """
        result = self.db.execute_query(query, (student_id, course_id))
        return result[0] if result else None